--IF EXISTS (
--	SELECT * FROM sys.views
--	WHERE (([name] = 'sea_people') AND (schema_id = SCHEMA_ID('dbo')))
IF OBJECT_ID('sea_people', 'V') IS NOT NULL
	DROP VIEW sea_people
GO
CREATE VIEW sea_people AS
(SELECT sea, COUNT(PLACE_OF_REST.id) AS places,
	(SELECT COUNT(PERSON.id)
		FROM PLACE_TOUR
			JOIN PLACE_TIME ON (PLACE_TIME.place_id = PLACE_TOUR.id)
			JOIN [GROUP] ON ([GROUP].id = PLACE_TIME.group_id)
			JOIN PERSON_GROUP ON (PERSON_GROUP.group_id = [GROUP].id)
			JOIN PERSON ON (PERSON.id = PERSON_GROUP.person_id)
		WHERE (PLACE_TOUR.place_id IN (SELECT place_id 
											FROM SEA	
												JOIN SEA_PLACE ON (SEA.id = SEA_PLACE.sea_id)
												JOIN PLACE_OF_REST ON (SEA_PLACE.place_id = PLACE_OF_REST.id)
											WHERE (SEA.sea = S.sea)
									   ))
	) AS people,
	(SELECT SUM(price - price * discount / 100) / SUM(DAY(end_date - start_date))
				FROM [GROUP]
					JOIN PLACE_TIME ON ([GROUP].id = PLACE_TIME.group_id)
				WHERE (PLACE_TIME.place_id IN (SELECT PLACE_TOUR.id
												FROM PLACE_TOUR
													JOIN PLACE_OF_REST ON (PLACE_OF_REST.id = PLACE_TOUR.place_id)
													JOIN SEA_PLACE ON (SEA_PLACE.place_id = PLACE_OF_REST.id)
													JOIN SEA ON (SEA_PLACE.sea_id = SEA.id)
												WHERE (SEA.sea = S.sea)
											   ))
	) AS money
	FROM SEA S	
		JOIN SEA_PLACE ON (S.id = SEA_PLACE.sea_id)
		JOIN PLACE_OF_REST ON (SEA_PLACE.place_id = PLACE_OF_REST.id)
	GROUP BY sea
)
GO
SELECT *
	FROM sea_people
